package com.pub.dbtools;

import net.sf.json.JSONArray;
import net.sf.json.JsonConfig;
import net.sf.json.util.CycleDetectionStrategy;
import org.jeecgframework.core.util.PropertiesUtil;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Hashtable;
import java.util.List;


public class DataSimpDto {

	private ArrayList arraydata;
	private ArrayList arraydatamap;
	private int totrec;

	public DataSimpDto(ArrayList newvec) {
		arraydata = newvec;
	}

	public DataSimpDto(String sql, int npageno, int npagenum) {

		arraydata = DBQuery.makeQuery(sql, npageno, npagenum);
		if (arraydata == null) {
			arraydata = new ArrayList();
			String tmparr[] = { "nullerror" };
			arraydata.add(tmparr);
		}
		String totstr = (String) arraydata.get(arraydata.size() - 1);
		arraydata.remove(arraydata.size() - 1);
		totrec = Integer.parseInt(totstr);
	}

	public DataSimpDto(String dbname, String sql) {
		if (dbname.equals("")) {
			arraydata = DBQuery.makeQuery(sql);
		} else {
			if (dbname.toLowerCase().equals("operdb")) {
				PropertiesUtil tmpvo = new PropertiesUtil("dbconfig.properties");
				dbname = tmpvo.readProperty("jdbc.dbType");
			}
			arraydata = DBQuery.makeQueryDb(dbname, sql);
		}
		if (arraydata == null) {
			arraydata = new ArrayList();
			String tmparr[] = { "nullerror" };
			arraydata.add(tmparr);
		}
	}

	public DataSimpDto(String sql) {
		arraydata = DBQuery.makeQuery(sql);
		if (arraydata == null) {
			arraydata = new ArrayList();
			String tmparr[] = { "nullerror" };
			arraydata.add(tmparr);
		}
		arraydatamap=getMaplist();
	}
    private ArrayList getMaplist(){
        ArrayList  maplist=new ArrayList();
        for (int i=0;i<getTotRecord();i++){
			HashMap mvalue=new HashMap<>();
			for(int j=0;j<getTotField();j++){
				mvalue.put(getFieldName(j),getDataValue(j,i));
            }
            maplist.add(mvalue);
		}
       return maplist;
	}

	public JSONArray toPageContHash(Hashtable<String, String> tmphash) {

		String page = tmphash.get("page");
		String rows = tmphash.get("rows");
		String type = tmphash.get("type");

		String fromsql = tmphash.get("formsql");
		String ordersql = tmphash.get("ordersql");
		String withsql = tmphash.get("withsql");
		String groupsql = tmphash.get("groupsql");
		String wheresql = tmphash.get("wheresql");
		String selesql = tmphash.get("selesql");
		if (!fromsql.equals("")) {
			if (!fromsql.trim().toLowerCase().startsWith("from ")) {
				fromsql = "from " + fromsql;
			}
		}
		if (!wheresql.equals("")) {
			if (!wheresql.trim().toLowerCase().startsWith("where ")) {
				wheresql = "where " + wheresql;
			}
		}
		if (!ordersql.equals("")) {

			if (!ordersql.trim().toLowerCase().startsWith("order ")) {
				ordersql = "order by " + ordersql;
			}
		}
		if (!groupsql.equals("")) {

			if (!groupsql.trim().toLowerCase().startsWith("group ")) {
				groupsql = "group by " + groupsql;
			}
		}
		if (!selesql.equals("")) {

			if (!selesql.trim().toLowerCase().startsWith("select ")) {
				selesql = "select " + selesql;
			}
		}

		if (page.equals("")) {
			page = "0";
		}
		if (rows.equals("")) {
			rows = "0";
		}
		int npage = Integer.parseInt(page);
		int nrows = Integer.parseInt(rows);

		int nowRecord = (int) ((npage - 1) * nrows);

		DataTools tmptools = new DataTools();
		String nsize = String.valueOf(rows);
		int npagesize = Integer.parseInt(nsize);

		String sumsql = "select count(*) as nowtot " + fromsql + " " + wheresql;
		DataSimpDto sumdto = new DataSimpDto(sumsql);
		int nsumnum = 0;
		if (sumdto.getTotRecord() > 0) {
			String nowstr = sumdto.getDataValue("nowtot", 0);
			if (!nowstr.equals("")) {
				nsumnum = Integer.parseInt(nowstr);
			}
		}
		JSONArray json = new JSONArray();
		if (nsumnum < nowRecord) {
			json = new JSONArray();
			if (type.equals("1")) {
				return json;
			}
		} else {
			if (nowRecord >= 0) {
				String sqlstr = tmptools.intiQuerySQL(withsql, selesql,
						fromsql, wheresql, ordersql, nowRecord, npagesize,
						groupsql);

				DataSimpDto ddDsd = new DataSimpDto();

				json = ddDsd.toPageCont(sqlstr, npage, nowRecord);
			}
		}
		return json;

	}

	public JSONArray toPageCont(String sql, int npage, int nowred) {

		List<Object> tmplist = new ArrayList<Object>();

		if (npage < 10) {
			DataSimpDto dto = new DataSimpDto(sql);
			for (int s = 0; s < dto.getTotRecord(); s++) {
				Hashtable tmphash = new Hashtable();
				for (int t = 0; t < dto.getTotField(); t++) {
					String fieldname = dto.getFieldName(t).toLowerCase();
					String values = dto.getDataValue(fieldname, s);
					tmphash.put(fieldname, values);
				}
				tmplist.add(tmphash);
			}
		}

		JsonConfig jsonConfig = new JsonConfig();
		jsonConfig.setCycleDetectionStrategy(CycleDetectionStrategy.LENIENT);
		JSONArray json = JSONArray.fromObject(tmplist, jsonConfig);

		return json;
	}
	public JSONArray toPageCont(String sql) {

		List<Object> tmplist = new ArrayList<Object>();


		DataSimpDto dto = new DataSimpDto(sql);
		for (int s = 0; s < dto.getTotRecord(); s++) {
			Hashtable tmphash = new Hashtable();
			for (int t = 0; t < dto.getTotField(); t++) {
				String fieldname = dto.getFieldName(t).toLowerCase();
				String values = dto.getDataValue(fieldname, s);
				tmphash.put(fieldname, values);
			}
			tmplist.add(tmphash);
		}

		JsonConfig jsonConfig = new JsonConfig();
		jsonConfig.setCycleDetectionStrategy(CycleDetectionStrategy.LENIENT);
		JSONArray json = JSONArray.fromObject(tmplist, jsonConfig);

		return json;
	}
	public JSONArray toNullCont() {

		List<Object> tmplist = new ArrayList<Object>();


		JsonConfig jsonConfig = new JsonConfig();
		jsonConfig.setCycleDetectionStrategy(CycleDetectionStrategy.LENIENT);
		JSONArray json = JSONArray.fromObject(tmplist, jsonConfig);

		return json;
	}

	public DataSimpDto(String sql, int recnum) {
		arraydata = DBQuery.makeQuery(sql, recnum);
		if (arraydata == null) {
			arraydata = new ArrayList();
			String tmparr[] = { "nullerror" };
			arraydata.add(tmparr);
		}
	}

	public DataSimpDto(String sql, int recnum, String sign) {
		PropertiesUtil tmpvo = new PropertiesUtil("dbconfig.properties");
		String dbname = tmpvo.readProperty("jdbc.dbType");
		if (dbname.toLowerCase().startsWith("oracle")) {
			String newsql = "select * from (select my_table.*,rownum as my_rownum from(";
			newsql = newsql + sql
					+ ") my_table) where my_rownum>=0 and my_rownum<=" + recnum;

			arraydata = DBQuery.makeQuery(newsql, recnum);
		}
		if (dbname.toLowerCase().startsWith("mysql")) {
			String newsql = sql + " limit 0," + recnum;
			arraydata = DBQuery.makeQuery(newsql, recnum);
		}
		if (dbname.toLowerCase().startsWith("db2")) {
			String newsql = sql + " fetch first " + recnum + " rows only ";
			arraydata = DBQuery.makeQuery(newsql, recnum);
		}
		if (arraydata == null) {
			arraydata = new ArrayList();
			String tmparr[] = { "nullerror" };
			arraydata.add(tmparr);
		}

	}

	public DataSimpDto(String dbname, String sql, int recnum) {
		if (dbname.equals("")) {
			arraydata = DBQuery.makeQuery(sql, recnum);
		} else {
			arraydata = DBQuery.makeQuery(dbname, sql, recnum);
		}
		if (arraydata == null) {
			arraydata = new ArrayList();
			String tmparr[] = { "nullerror" };
			arraydata.add(tmparr);
		}
	}

	public boolean isNull() {
		if (arraydata == null) {
			return true;
		} else {
			String[] tmptrr = (String[]) arraydata.get(0);
			if ((tmptrr[0]).equals("nullerror")) {
				return true;
			}
			return false;
		}
	}

	public DataSimpDto() {
		arraydata = new ArrayList();
	}

	public final void clear() {
		arraydata = new ArrayList();
	}

	public final ArrayList getDataDto() {
		return arraydata;
	}

	public final ArrayList getDataDtomap() {


		return arraydatamap;
	}

	public final int getTotField() {
		String[] tmpstr = (String[]) arraydata.get(0);
		return tmpstr.length;
	}

	public final int getTotRecord() {
		return arraydata.size() - 1;
	}

	public final String getFieldName(int n) {
		String[] tmpstr = (String[]) arraydata.get(0);
		return tmpstr[n];
	}

	public final String[] getFieldName() {
		String[] tmpstr = (String[]) arraydata.get(0);
		return tmpstr;
	}

	public final String getDataValue(String name, int recordno) {
		return DataDtoSupclass.getDataValue(arraydata, name.trim(), recordno);
	}

	//public final String getDateValue(String name, int recordno) {
	//	String rqstr = DataDtoSupclass.getDataValue(arraydata, name.trim(),
	//			recordno).trim();
	//	if (rqstr.equals(""))
	//		return rqstr;

	//	return StaticTools.tostanddate(rqstr);
	//}

	public final String getNumValue(String name, int recordno) {
		String nowvalue = DataDtoSupclass.getDataValue(arraydata, name.trim(),
				recordno);
		if (nowvalue.equals("")) {
			nowvalue = "0";
		}
		return nowvalue;
	}

	public final String getDataValue(int fieldno, int recordno) {
		return DataDtoSupclass.getDataValue(arraydata, fieldno, recordno);
	}

	public final HashMap getDataIndex(String name) {
		HashMap data = new HashMap();
		if (getTotRecord() > 0) {
			for (int i = 0; i < getTotRecord(); i++) {
				String value = getDataValue(name.trim(), i);
				if (data.containsKey(value)) {
					ArrayList indexArry = (ArrayList) data.get(value);
					indexArry.add("" + i);
					data.put(value, indexArry);
				} else {
					ArrayList indexArry = new ArrayList();
					indexArry.add("" + i);
					data.put(value, indexArry);
				}
			}
		}
		return data;
	}

	public final void setDataValue(String name, int recordno, String newvalue) {
		String[] tmparr = (String[]) arraydata.get(0);

		this.arraydata = DataDtoSupclass.setDataValue(arraydata, name.trim(),
				recordno, newvalue);
	}

	public final void setDataValue(int fieldno, int recordno, String newvalue) {
		this.arraydata = DataDtoSupclass.setDataValue(arraydata, fieldno,
				recordno, newvalue);
	}

	public DataSimpDto(String selectsql, String fromSql, String searWhere,
			String searOrder, String groutpSql, String withSql, String kyename,
			String keyvalue) {
		String sql = makeStandSql(selectsql, fromSql, searWhere, searOrder,
				groutpSql, withSql, kyename, keyvalue);
		arraydata = DBQuery.makeQuery(sql);
		if (arraydata == null) {
			arraydata = new ArrayList();
			String tmparr[] = { "nullerror" };
			arraydata.add(tmparr);
		}
	}

	public final int getFullRecord() {
		return totrec;
	}

	public DataSimpDto(String selectsql, String fromSql, String searWhere,
			String searOrder, String groutpSql, String withSql, int num) {
		String sql = makeStandSql(selectsql, fromSql, searWhere, searOrder,
				groutpSql, withSql, num);
		arraydata = DBQuery.makeQuery(sql);
		if (arraydata == null) {
			arraydata = new ArrayList();
			String tmparr[] = { "nullerror" };
			arraydata.add(tmparr);
		}
	}

	private String makeStandSql(String selectsql, String fromSql,
			String searWhere, String searOrder, String groupSql,
			String withSql, int num) {
		String retsql = intiQuerySQL(withSql + selectsql, fromSql, searWhere,
				searOrder, groupSql, num);
		return retsql;
	}

	private String makeStandSql(String selectsql, String fromSql,
			String searWhere, String searOrder, String groupSql,
			String withSql, String keyname, String keyvalue) {
		String retsql = intiQuerySQL(withSql + selectsql, fromSql, searWhere,
				searOrder, keyname, keyvalue, groupSql);
		return retsql;
	}

	private String intiQuerySQL(String sql, String fromsql, String wheresql,
			String orderSql, String currKeyName, String currKeyValue,
			String groupsql) {
		PropertiesUtil tmpvo = new PropertiesUtil("dbconfig.properties");
		String dbname = tmpvo.readProperty("jdbc.dbType");
		StringBuffer querySQL = new StringBuffer();
		sql = sql.toLowerCase();
		if (dbname.toLowerCase().startsWith("oracle")) {
			querySQL.append(
					"select * from (select my_table.*,rownum as my_rownum from(")
					.append(sql + " " + fromsql + " " + wheresql + " "
							+ groupsql + " " + orderSql)
					.append(") my_table ")
					.append(") where " + currKeyName + "='" + currKeyValue
							+ "'");
		}
		if (dbname.toLowerCase().startsWith("mysql")) {
			if (!wheresql.trim().equals("")) {
				wheresql += " and ";
			}
			wheresql += currKeyName + "='" + currKeyValue;
			querySQL.append(sql + " " + fromsql + " " + wheresql + " "
					+ groupsql + " " + orderSql);
		}
		if (dbname.toLowerCase().startsWith("db2")) {
			if (!wheresql.trim().equals("")) {
				wheresql += " and ";
			}
			wheresql += currKeyName + "='" + currKeyValue;
			querySQL.append(sql + " " + fromsql + " " + wheresql + " "
					+ groupsql + " " + orderSql);
		}
		return querySQL.toString();
	}

	private String intiQuerySQL(String sql, String fromsql, String wheresql,
			String orderSql, String groupsql, int startIndex) {
		PropertiesUtil tmpvo = new PropertiesUtil("dbconfig.properties");
		String dbname = tmpvo.readProperty("jdbc.dbType");

		StringBuffer querySQL = new StringBuffer();

		int totrow = 1 + startIndex;
		if (dbname.toLowerCase().startsWith("oracle")) {
			querySQL.append(
					"select * from (select my_table.*,rownum as my_rownum from(")
					.append(sql + " " + fromsql + " " + wheresql + " "
							+ groupsql + " " + orderSql).append(") my_table ")
					.append(") where my_rownum>=")
					.append(startIndex + " and my_rownum<" + totrow);
		}
		if (dbname.toLowerCase().startsWith("mysql")) {
			querySQL.append(
					sql + " " + fromsql + " " + wheresql + " " + groupsql + " "
							+ orderSql).append(
					"limit " + startIndex + "," + totrow);
		}
		if (dbname.toLowerCase().startsWith("db2")) {
			sql = sql + ",rownumber() over(" + orderSql + ") as rowid ";
			sql += fromsql + " " + wheresql + " " + groupsql;

			sql = "select * from (" + sql + ") as a where a.rowid > "
					+ startIndex + " AND a.rowid <=" + totrow;
			return sql;
		}

		return querySQL.toString();
	}
}
